package com.zucai365.football.dao.impl;

import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.wcj.common.dao.impl.WcjDaoImpl;
import com.zucai365.football.bean.TeamPlayerBean;
import com.zucai365.football.dao.TeamPlayerDao;

@Repository("teamPlayerDao")
public class TeamPlayerDaoImpl extends WcjDaoImpl<TeamPlayerBean> implements
		TeamPlayerDao {

	@Override
	public void saveTeamPlayerPlayTime( Long lianSaiId, Long lianSaiYearId ) {
		
		final StringBuffer sql = new StringBuffer();
		sql.append( " UPDATE t_team_player A " );
		sql.append( " ,(" );
		sql.append( " SELECT t1.ID AS FID,t1.FOOTBALL_TEAM_NAME,T3.ID AS PID,t3.PLAYER_NAME,SUM(T4.PLAYING_SUM_TIME) AS PLAYING_SUM_TIME" );
		sql.append( " FROM T_FOOTBALL_TEAM t1 , T_TEAM_PLAYER t2,t_football_player t3,t_shoufa t4" );
		sql.append( " where t1.ID=t2.FOOTBALL_TEAM_ID" );
		sql.append( " and t2.LIANSAI_ID="+lianSaiId+" and t2.LIANSAI_YEAR_ID="+lianSaiYearId );
		sql.append( " and t3.id=t2.FOOTBALL_PLAYER_ID" );
		sql.append( " and t4.FOOTBALL_PLAYER_ID=t3.ID and t4.FOOTBALL_TEAM_ID=t1.id" );
		sql.append( " AND t4.PLAYING_SUM_TIME>0" );
		sql.append( " GROUP BY FID,FOOTBALL_TEAM_NAME,PID,PLAYER_NAME" );
		sql.append( " ORDER BY PLAYING_SUM_TIME DESC" );
		sql.append( " ) B");
		sql.append( " SET A.PLAY_SUM_TIME=B.PLAYING_SUM_TIME");
		sql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.PID AND A.FOOTBALL_TEAM_ID=B.FID");
				
		System.out.println( sql.toString() );
		this.getHibernateTemplate().execute( new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				session.createSQLQuery(sql.toString()).executeUpdate();
				return null;
			}
		});
		
	}
	
	/**
	 * 统计球员进球情况
	 * @param lianSaiYearId
	 * @param lianSaiId
	 */
	public void saveTeamPlayerJinQiu(  Long lianSaiYearId, Long lianSaiId ){
		
		final StringBuffer personSumJinQinSql = new StringBuffer();
		personSumJinQinSql.append( " UPDATE t_team_player AA" );
		personSumJinQinSql.append( " ,(" );
		personSumJinQinSql.append( " SELECT B.ID AS FID ,A.EVENT_FOOTBALL_TEAM_ID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,B.PLAYER_NAME,COUNT(A.`EVENT`) AS JINQIUCOUNT " );
		personSumJinQinSql.append( " FROM t_game_fire A , t_football_player B " );
		personSumJinQinSql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.ID " );
		personSumJinQinSql.append( " AND A.LIANSAI_ID="+lianSaiId+" AND A.LIANSAI_YEAR_ID="+lianSaiYearId );
		personSumJinQinSql.append( " AND A.EVENT IN ('进球','点球','乌龙球')" );
		personSumJinQinSql.append( " GROUP BY  B.ID,A.EVENT_FOOTBALL_TEAM_ID,B.PLAYER_NAME,A.LIANSAI_ID,A.LIANSAI_YEAR_ID" );
		personSumJinQinSql.append( " ORDER BY JINQIUCOUNT DESC" );
		personSumJinQinSql.append( " ) BB" );
		personSumJinQinSql.append( " SET AA.SUM_JINQIU=BB.JINQIUCOUNT" );
		personSumJinQinSql.append( " WHERE AA.FOOTBALL_PLAYER_ID = BB.FID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		personSumJinQinSql.append( " AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		final StringBuffer personZCJinQinSql = new StringBuffer();
		personZCJinQinSql.append( " UPDATE t_team_player AA" );
		personZCJinQinSql.append( " ,(" );
		personZCJinQinSql.append( " SELECT B.ID AS FID ,A.EVENT_FOOTBALL_TEAM_ID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,B.PLAYER_NAME,COUNT(A.`EVENT`) AS JINQIUCOUNT " );
		personZCJinQinSql.append( " FROM t_game_fire A , t_football_player B " );
		personZCJinQinSql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.ID " );
		personZCJinQinSql.append( " AND A.LIANSAI_ID="+lianSaiId+" AND A.LIANSAI_YEAR_ID="+lianSaiYearId );
		personZCJinQinSql.append( " AND A.EVENT_STATUS='主场'" );
		personZCJinQinSql.append( " AND A.EVENT IN ('进球','点球','乌龙球')" );
		personZCJinQinSql.append( " GROUP BY  B.ID,A.EVENT_FOOTBALL_TEAM_ID,B.PLAYER_NAME,A.LIANSAI_ID,A.LIANSAI_YEAR_ID" );
		personZCJinQinSql.append( " ORDER BY JINQIUCOUNT DESC" );
		personZCJinQinSql.append( " ) BB" );
		personZCJinQinSql.append( " SET AA.ZC_JINQIU=BB.JINQIUCOUNT" );
		personZCJinQinSql.append( " WHERE AA.FOOTBALL_PLAYER_ID = BB.FID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		personZCJinQinSql.append( " AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		final StringBuffer personKCJinQinSql = new StringBuffer();
		personKCJinQinSql.append( " UPDATE t_team_player AA" );
		personKCJinQinSql.append( " ,(" );
		personKCJinQinSql.append( " SELECT B.ID AS FID ,A.EVENT_FOOTBALL_TEAM_ID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,B.PLAYER_NAME,COUNT(A.`EVENT`) AS JINQIUCOUNT " );
		personKCJinQinSql.append( " FROM t_game_fire A , t_football_player B " );
		personKCJinQinSql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.ID " );
		personKCJinQinSql.append( " AND A.LIANSAI_ID="+lianSaiId+" AND A.LIANSAI_YEAR_ID="+lianSaiYearId );
		personKCJinQinSql.append( " AND A.EVENT_STATUS='客场'" );
		personKCJinQinSql.append( " AND A.EVENT IN ('进球','点球','乌龙球')" );
		personKCJinQinSql.append( " GROUP BY  B.ID,A.EVENT_FOOTBALL_TEAM_ID,B.PLAYER_NAME,A.LIANSAI_ID,A.LIANSAI_YEAR_ID" );
		personKCJinQinSql.append( " ORDER BY JINQIUCOUNT DESC" );
		personKCJinQinSql.append( " ) BB" );
		personKCJinQinSql.append( " SET AA.KC_JINQIU=BB.JINQIUCOUNT" );
		personKCJinQinSql.append( " WHERE AA.FOOTBALL_PLAYER_ID = BB.FID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		personKCJinQinSql.append( " AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		this.getHibernateTemplate().execute( new HibernateCallback() {
			@Override
			public Object doInHibernate( Session session ) throws HibernateException,
					SQLException {
				
				session.createSQLQuery( personSumJinQinSql.toString() ).executeUpdate();
				session.createSQLQuery( personZCJinQinSql.toString() ).executeUpdate();
				session.createSQLQuery( personKCJinQinSql.toString() ).executeUpdate();
				
				return null;
			}
		});
	}
	
	@Override
	public void saveTeamPlayerHongHuangPai(Long lianSaiYearId, Long lianSaiId) {
		
		final StringBuffer huangSql = new StringBuffer();
		huangSql.append( " UPDATE t_team_player AA" );
		huangSql.append( " ,(" );
		huangSql.append( " SELECT B.ID AS FID ,A.EVENT_FOOTBALL_TEAM_ID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,B.PLAYER_NAME,COUNT(A.`EVENT`) AS PAICOUNT " );
		huangSql.append( " FROM t_game_fire A , t_football_player B " );
		huangSql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.ID " );
		huangSql.append( " AND A.LIANSAI_ID="+lianSaiId+" AND A.LIANSAI_YEAR_ID="+lianSaiYearId );
		huangSql.append( " AND A.EVENT='黄牌'" );
		huangSql.append( " GROUP BY  B.ID,A.EVENT_FOOTBALL_TEAM_ID,B.PLAYER_NAME,A.LIANSAI_ID,A.LIANSAI_YEAR_ID" );
		huangSql.append( " ORDER BY PAICOUNT DESC" );
		huangSql.append( " ) BB" );
		huangSql.append( " SET AA.SUM_HUANG=BB.PAICOUNT" );
		huangSql.append( " WHERE AA.FOOTBALL_PLAYER_ID = BB.FID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		huangSql.append( " AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		final StringBuffer hongSql = new StringBuffer();
		hongSql.append( " UPDATE t_team_player AA" );
		hongSql.append( " ,(" );
		hongSql.append( " SELECT B.ID AS FID ,A.EVENT_FOOTBALL_TEAM_ID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,B.PLAYER_NAME,COUNT(A.`EVENT`) AS PAICOUNT " );
		hongSql.append( " FROM t_game_fire A , t_football_player B " );
		hongSql.append( " WHERE A.FOOTBALL_PLAYER_ID=B.ID " );
		hongSql.append( " AND A.LIANSAI_ID="+lianSaiId+" AND A.LIANSAI_YEAR_ID="+lianSaiYearId );
		hongSql.append( " AND A.EVENT IN ('红牌','两黄变红')" );
		hongSql.append( " GROUP BY  B.ID,A.EVENT_FOOTBALL_TEAM_ID,B.PLAYER_NAME,A.LIANSAI_ID,A.LIANSAI_YEAR_ID" );
		hongSql.append( " ORDER BY PAICOUNT DESC" );
		hongSql.append( " ) BB" );
		hongSql.append( " SET AA.SUM_HONG=BB.PAICOUNT" );
		hongSql.append( " WHERE AA.FOOTBALL_PLAYER_ID = BB.FID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		hongSql.append( " AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		this.getHibernateTemplate().execute( new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				session.createSQLQuery( huangSql.toString()).executeUpdate();
				session.createSQLQuery( hongSql.toString()).executeUpdate();
				return null;
			}
		});
		
	}
}
